package org.bees.cms.web.util.velocity.global;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import net.sf.json.JSONObject;

import org.bees.cms.common.Mappers;
import org.bees.cms.common.Order;
import org.bees.cms.common.ThreadLocals;
import org.bees.cms.core.clause.Adapter;
import org.bees.cms.core.clause.Criterion;
import org.bees.cms.core.clause.Restriction;
import org.bees.cms.core.clause.Sorting;
import org.bees.cms.core.setting.Constant;
import org.bees.cms.web.util.JPAAdapter;
import org.bees.cms.web.util.velocity.Configuration;

public class Querier {
	private static Adapter adapter = new JPAAdapter();

	public List<?> list(String table, String condition, String order, String by, int... limits) throws Exception {
		EntityManager manager = ThreadLocals.ENTITY_MANAGERS.get();
		Class<?> mapper = Configuration.getTable(table);

		String _table = Mappers.getTableName(mapper);
		String _order = order != null && !order.trim().equals("") ? Order.valueOf(order.toUpperCase()).name() : "";
		String _by = by != null && !by.trim().equals("") ? Mappers.getColumnName(mapper, by) : "";

		String _condition = "";
		if (condition != null && condition.startsWith("{") && condition.endsWith("}")) {
			Criterion criterion = new Criterion();
			criterion.from(JSONObject.fromObject(condition));
			_condition = criterion.toSQL(adapter, mapper, "o");
		}

		String WHERE = _condition.trim().equals("") ? "" : "WHERE " + _condition + " ";
		String ORDER = !_by.trim().equals("") ? "ORDER BY " + _by + " " + _order : "";

		String sql4domains = "SELECT * FROM " + _table + " " + WHERE + ORDER;
		Query query = manager.createNativeQuery(sql4domains, mapper);

		if (limits != null && limits.length >= 2 && limits[0] > 0 && limits[1] > 0) {
			query.setFirstResult((limits[0] - 1) * limits[1]);
			query.setMaxResults(limits[1]);
		}

		return query.getResultList();
	}

	public List<?> query(Class<?> mapper, String sql, int... limits) {
		EntityManager manager = ThreadLocals.ENTITY_MANAGERS.get();
		Query query = manager.createNativeQuery(sql, mapper);
		if (limits != null && limits.length >= 2 && limits[0] > 0 && limits[1] > 0) {
			query.setFirstResult((limits[0] - 1) * limits[1]);
			query.setMaxResults(limits[1]);
		}
		return query.getResultList();
	}

	public List<?> query(Class<?> mapper, Criterion criterion, Sorting sorting, Restriction restriction) throws Exception {
		EntityManager manager = ThreadLocals.ENTITY_MANAGERS.get();
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT * FROM ").append(Mappers.getTableName(mapper));
		builder.append(criterion == null ? "" : " " + criterion.toSQL(adapter, mapper, "o"));
		builder.append(sorting == null ? "" : " " + sorting.toSQL(adapter, mapper, "o"));
		Query query = manager.createNativeQuery(builder.toString(), mapper);
		if (restriction != null) {
			query.setFirstResult((restriction.getCurrent() - 1) * restriction.getCapacity());
			query.setMaxResults(restriction.getCapacity());
		}
		return query.getResultList();
	}

	public String constant(String expression) throws Exception {
		try {
			EntityManager manager = ThreadLocals.ENTITY_MANAGERS.get();
			Query query = manager.createQuery("SELECT c FROM Constant AS c WHERE c.expression = :expression");
			query.setParameter("expression", expression);
			Constant constant = (Constant) query.getSingleResult();
			return constant.getValue();
		} catch (Exception e) {
			return "";
		}
	}

}
